package cn.tedu;
//增删改查
import org.junit.Test;

import java.sql.*;

public class Demo03 {

    @Test //报错 alt+回车 选择 junit4
    public void insert() throws SQLException {
        System.out.println("insert");
            //1. 获取数据库连接对象
            Connection conn =
                    DriverManager.getConnection(
                            "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","root");
            System.out.println(conn);
            //2. 创建执行SQL语句的对象
            Statement s = conn.createStatement();
            //3. 执行SQL语句 execute=执行
            s.execute("insert into emp(name) values('Tom')");
            //4. 关闭资源
            conn.close();
            System.out.println("执行完成!");
        }
    @Test
    public void delete() throws SQLException {
        System.out.println("delete");
        //1. 获取数据库连接对象
        Connection conn =
                DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","root");
        System.out.println(conn);
        //2. 创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3. 执行SQL语句 execute=执行
        s.execute("delete from emp where name='Jerry'");
        //4. 关闭资源
        conn.close();
        System.out.println("执行完成!");
    }@Test
    public void update() throws SQLException {
        System.out.println("update");
        //1. 获取数据库连接对象
        Connection conn =
                DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","root");
        System.out.println(conn);
        //2. 创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3. 执行SQL语句 execute=执行
        s.execute("update emp set name='Jerry' where name='Tom'");
        //4. 关闭资源
        conn.close();
        System.out.println("执行完成!");
    }
    @Test
    public void select() throws SQLException {
        System.out.println("select");
        //1. 获取数据库连接对象
        Connection conn =
                DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","root");
        System.out.println(conn);
        //2. 创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3. 执行SQL语句 返回值为结果集对象 里面装着查询回来的结果
        ResultSet rs =
                s.executeQuery("select name, job,sal from emp");
        //4.遍历结果 next下一个,让游标下游,返回值表示是否有下一条数据
        while (rs.next()){
            //从结果集对象中获取数据
            //1.通过字段名获取
            //String name = rs.getString("name");
            //2.通过字段的位置获取数据
            String name = rs.getString(1);
            String job = rs.getString(2);
            double sal = rs.getDouble(3);
            System.out.println(name+":"+job+":"+sal);
        }
        //4. 关闭资源
        conn.close();
        System.out.println("执行完成!");
    }

}
